/*
 * Copyright (c) 2010 CCX(China) Co.,Ltd. All Rights Reserved.
 *
 * This software is the confidential and proprietary information of
 * CCX(China) Co.,Ltd. ("Confidential Information").
 * It may not be copied or reproduced in any manner without the express 
 * written permission of CCX(China) Co.,Ltd.
 *
 * @author luosisi
 * Date: 2010-7-5 上午11:00:47
 */
package com.ccxe.manage.dao.impl;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.ccxe.manage.dao.UserDAO;
import com.ccxe.manage.db.CcxDB;
import com.ccxe.manage.db.DBFactory;
import com.ccxe.manage.model.CustomerBean;
import com.ccxe.manage.model.UserBean;
import com.ccxe.manage.model.UserRoleBean;
import com.ccxe.manage.service.transaction.TransactionCcxDb;
import com.ccxe.manage.util.StringValueValid;

/**
 * @author luosisi
 * 
 */
public class UserDaoImpl extends UserDAO {

	public static String SEARCH_ALL_USER = "select users.USER_ID,users.USER_NAME, users.USER_TYPE, " +
		 " users.USER_DISABLED, users.USER_DELETED, users.LAST_LOGIN_TIME, users.EMAIL_ADDRESS, users.EMAIL_NOTIFICATIONS ," +
		 "  customer.COMPANY, role.role_name" +
		 " from C_USER users,C_CUSTOMER  customer,C_USER_ROLE userrole,C_ROLE role" +
		 " where users.customer_id = customer.customer_id" +
		 " and users.USER_DELETED = 0" +
    	 " and users.USER_ID = userrole.USER_ID(+)" + 
	     " and userrole.ROLE_ID = role.ROLE_ID(+)";
	
	/**
	 * 增加用户
	 */
	public int addUser(UserBean user) {
		TransactionCcxDb ccxDB = null;
		int rs = 0;
		try {
			if (user != null) {
				ccxDB =  new TransactionCcxDb();
				ccxDB.beginTransaction();
				String sqlKey = "insert into C_user(USER_ID,USER_NAME,CUSTOMER_ID,USER_TYPE,"
						+ " USER_DISABLED,EMAIL_ADDRESS,EMAIL_NOTIFICATIONS) "
						+ " values(?,?,?,?,?,?,?)";
				String user_pwd_Sql = "insert into C_user_password(USER_ID,PASSWORD)values(?,?)";
				String user_role_Sql = "insert into C_USER_ROLE(USER_ID,ROLE_ID)values(?,?)";
				Object[] items = new Object[7];
				items[0] = user.getUserID();
				items[1] = user.getUserName();
				items[2] = user.getCustomerID();
				items[3] = user.getUserType();
				items[4] = 1;// user.getUserDisable()
				items[5] = user.getEmailAddress();
				items[6] = user.getEmailNotifications();
				Object[] items_pwd = new Object[2];
				items_pwd[0] = user.getUserID();
				items_pwd[1] = user.getUserPasswordBean().getPassword();
				Object[] items_role = new Object[2];
				items_role[0] = user.getUserID();
				items_role[1] = user.getUserRole().getRoleID();

				int returnNumUser = ccxDB.executeUpdate(sqlKey, items);
				int returnNumPwd = ccxDB.executeUpdate(user_pwd_Sql, items_pwd);
				int returnNumRole = ccxDB.executeUpdate(user_role_Sql, items_role);
				ccxDB.commit();
				if(returnNumUser>0 && returnNumPwd>0 && returnNumRole>0){
					rs = 1;
				}
			}
		} catch (Exception e) {
			try {
				ccxDB.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			rs = 0;
			e.printStackTrace();
		} finally {
			try {
				if (ccxDB != null)
					ccxDB.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return rs;
	}

	/**
	 * 删除用户
	 * 
	 * @param uid 根据用户id删除用户
	 * @param cid 根据公司id删除用户
	 * @return
	 */
	public int deleteUser(String uid) {
		TransactionCcxDb ccxDB = null;
		int rs_for_user = 0;
		int rs_for_role = 0;
		try {
				if(uid!=null&&uid.length()>0){
					ccxDB =  new TransactionCcxDb();
					ccxDB.beginTransaction();
					String sqlKey = "update c_user set USER_DELETED='1' where USER_ID=? ";
					String[] items=new String[1];
					 items[0] =uid;
					 rs_for_role=new UserRoleDaoImpl().deleteUserRole(uid);
       				rs_for_user = ccxDB.executeUpdate(sqlKey, items);
       				ccxDB.commit();
       				if(rs_for_role>0&&rs_for_user>0){
       					return 1;
       				}
				}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (ccxDB != null)
					ccxDB.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}return 0;
	}


	/**
	 * 封装 私有的读取数据方法
	 * @param sqlKey 执行的sql语句
	 * @param values 参数, 无参传null
	 * @return
	 */
	private List<UserBean> getReadList(String sqlKey,Object[] values){
		CcxDB ccxDB = null;
		ResultSet rs = null;
		List<UserBean> list = new ArrayList<UserBean>();
		try {
					ccxDB = DBFactory.getInstance();
			if (sqlKey != null&&values!=null) {//有模糊查询条件
						rs = ccxDB.executeQuery(sqlKey, values);
			} else if(sqlKey!=null) {//无模糊查询条件,获得所有用户列表
						rs = ccxDB.executeQuery(sqlKey);
			}
			while (rs.next()) {
				UserBean ub = new UserBean();
				ub.setUserID(rs.getString("USER_ID"));
				ub.setUserName(rs.getString("USER_NAME"));
				ub.setCustomerID(rs.getString("CUSTOMER_ID"));
				ub.setUserDisable(rs.getString("USER_DISABLED"));
				ub.setLastLoginTime(rs.getDate("LAST_LOGIN_TIME"));
				ub.setEmailAddress(rs.getString("EMAIL_ADDRESS"));
				ub.setEmailNotifications(rs.getString("EMAIL_NOTIFICATIONS"));
				ub.setUserRoleName(rs.getString("ROLE_NAME"));
				CustomerBean customerBean = new CustomerBean();
				customerBean.setCustomerID(rs.getString("CUSTOMER_ID"));
				customerBean.setCompany(rs.getString("COMPANY"));
				customerBean.setContactName(rs.getString("CONTACT_NAME"));
				customerBean.setContactPhone(rs.getString("CONTACT_PHONE"));
				customerBean.setContactFax(rs.getString("CONTACT_FAX"));
				customerBean.setContactEmail(rs.getString("CONTACT_EMAIL"));
				customerBean.setStreet(rs.getString("STREET"));
				customerBean.setCity(rs.getString("CITY"));
				customerBean.setCountry(rs.getString("COUNTRY"));
				customerBean.setPostCode(rs.getString("POST_CODE"));
				customerBean.setLastUpdatetime(rs.getDate("LAST_UPDATETIME"));
				customerBean.setStatus(rs.getString("STATUS"));
				ub.setCustomerBean(customerBean);
				list.add(ub);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				if (ccxDB != null)
					ccxDB.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	/**
	 * 通过用户ID获得User
	 */
	public UserBean getById(String uid) {
		CcxDB ccxDB = null;
		ResultSet rs = null;
		UserBean ub = null;
		try {
			if (uid != null && !uid.equals("")) {
				String sqlKey = 
					"select users.*, customer.* ,userrole.role_id" +
					  " from c_USER users,c_CUSTOMER  customer,C_USER_ROLE userrole" +
					" where users.customer_id = customer.customer_id" +
					" and users.USER_DELETED = 0" +
					" and users.USER_ID = userrole.USER_ID(+) " +
					" and users.USER_ID=?"  ;
				Object[] items = new Object[1];
				items[0] = uid;
				ccxDB = DBFactory.getInstance();
				rs = ccxDB.executeQuery(sqlKey, items);
				if (rs.next()) {
					ub = new UserBean();
					ub.setUserID(uid);
					ub.setUserName(rs.getString("USER_NAME"));
					ub.setUserType(rs.getString("USER_TYPE"));
					ub.setCustomerID(rs.getString("CUSTOMER_ID"));
					ub.setUserDisable(rs.getString("USER_DISABLED"));
					ub.setLastLoginTime(rs.getDate("LAST_LOGIN_TIME"));
					ub.setEmailAddress(rs.getString("EMAIL_ADDRESS"));
					ub.setEmailNotifications(rs.getString("EMAIL_NOTIFICATIONS"));
					CustomerBean customerBean = new CustomerBean();
					customerBean.setCustomerID(rs.getString("CUSTOMER_ID"));
					customerBean.setCompany(rs.getString("COMPANY"));
					customerBean.setContactName(rs.getString("CONTACT_NAME"));
					customerBean.setContactPhone(rs.getString("CONTACT_PHONE"));
					customerBean.setContactFax(rs.getString("CONTACT_FAX"));
					customerBean.setContactEmail(rs.getString("CONTACT_EMAIL"));
					customerBean.setStreet(rs.getString("STREET"));
					customerBean.setCity(rs.getString("CITY"));
					customerBean.setCountry(rs.getString("COUNTRY"));
					customerBean.setPostCode(rs.getString("POST_CODE"));
					customerBean.setLastUpdatetime(rs.getDate("LAST_UPDATETIME"));
					customerBean.setStatus(rs.getString("STATUS"));
					ub.setCustomerBean(customerBean);
					UserRoleBean roleBean = new UserRoleBean();
					roleBean.setRoleID(rs.getString("ROLE_ID"));
					ub.setUserRole(roleBean);
				}
			}
		} catch (Exception e) {
			ub = null;
			e.printStackTrace();
		} finally {
			try {
				if (ccxDB != null)
					ccxDB.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return ub;
	}
	/**
	 * 通过用户ID获得User
	 */
	public UserBean getByIdIsExsit(String uid) {
		CcxDB ccxDB = null;
		ResultSet rs = null;
		UserBean ub = null;
		try {
			if (uid!=null&&!uid.equals("")) {
				String sqlKey = 
					"select users.*, customer.* ,userrole.role_id" +
					" from C_USER users,C_CUSTOMER  customer,C_USER_ROLE userrole" +
					" where users.customer_id = customer.customer_id" +
					" and users.USER_ID = userrole.USER_ID(+) " +
					" and users.USER_ID=?"  ;
				Object[] items = new Object[1];
				items[0] = uid;
				ccxDB = DBFactory.getInstance();
				rs = ccxDB.executeQuery(sqlKey, items);
				if (rs.next()) {
					ub = new UserBean();
					ub.setUserID(uid);
					ub.setUserName(rs.getString("USER_NAME"));
					ub.setUserType(rs.getString("USER_TYPE"));
					ub.setCustomerID(rs.getString("CUSTOMER_ID"));
					ub.setUserDisable(rs.getString("USER_DISABLED"));
					ub.setLastLoginTime(rs.getDate("LAST_LOGIN_TIME"));
					ub.setEmailAddress(rs.getString("EMAIL_ADDRESS"));
					ub.setEmailNotifications(rs.getString("EMAIL_NOTIFICATIONS"));
					CustomerBean customerBean = new CustomerBean();
					customerBean.setCustomerID(rs.getString("CUSTOMER_ID"));
					customerBean.setCompany(rs.getString("COMPANY"));
					customerBean.setContactName(rs.getString("CONTACT_NAME"));
					customerBean.setContactPhone(rs.getString("CONTACT_PHONE"));
					customerBean.setContactFax(rs.getString("CONTACT_FAX"));
					customerBean.setContactEmail(rs.getString("CONTACT_EMAIL"));
					customerBean.setStreet(rs.getString("STREET"));
					customerBean.setCity(rs.getString("CITY"));
					customerBean.setCountry(rs.getString("COUNTRY"));
					customerBean.setPostCode(rs.getString("POST_CODE"));
					customerBean.setLastUpdatetime(rs.getDate("LAST_UPDATETIME"));
					customerBean.setStatus(rs.getString("STATUS"));
					ub.setCustomerBean(customerBean);
					UserRoleBean roleBean = new UserRoleBean();
					roleBean.setRoleID(rs.getString("ROLE_ID"));
					ub.setUserRole(roleBean);
				}
			}
		} catch (Exception e) {
			ub = null;
			e.printStackTrace();
		} finally {
			try {
				if (ccxDB != null)
					ccxDB.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return ub;
	}

	/**
     * 更新用户 可更新字段:USER_NAME,USER_TYPE,USER_DISABLED,EMAIL_ADDRESS,
	 * EMAIL_NOTIFICATIONS
	 */
	public int updateUser(UserBean user,boolean isUpdate) {
		TransactionCcxDb ccxDB = null;
		int rs = 0;
		try {
			if (user != null) {
				ccxDB = new TransactionCcxDb();
				ccxDB.beginTransaction();
				String sqlKey = "update C_USER set "
						+ " USER_NAME=?,USER_TYPE=?,"
						+ " USER_DISABLED=?,EMAIL_ADDRESS=?,EMAIL_NOTIFICATIONS=?"
						+ " where USER_ID=?";
				Object[] items = new Object[6];
				items[0] = user.getUserName();
				items[1] = user.getUserType();
				items[2] = user.getUserDisable();
				items[3] = user.getEmailAddress();
				items[4] = user.getEmailNotifications();// 0接受,1不接受邮件提醒
				items[5] = user.getUserID();
				String sqlUserRole;
				Object[] items2;
				if(user.getUserRole().getRoleID() != null && !"".equals(user.getUserRole().getRoleID().trim())){
					if(isUpdate){
						sqlUserRole = "update C_USER_ROLE set ROLE_ID = ? where USER_ID=?";
					}else{
						sqlUserRole = "insert into C_USER_ROLE(ROLE_ID,USER_ID) values(?,?)";
					}
					items2 = new Object[2];
					items2[0] = user.getUserRole().getRoleID();
					items2[1] = user.getUserID();
				}else{
					sqlUserRole = "delete from C_USER_ROLE where USER_ID=?";
					items2 = new Object[1];
					items2[0] = user.getUserID();
				}
				int rs1 = ccxDB.executeUpdate(sqlKey, items);
				int rs2 = ccxDB.executeUpdate(sqlUserRole, items2);
				ccxDB.commit();
				if(rs1>0){
					if(rs2>0 || (rs2==0 && !isUpdate))
						rs = 1;
				}
			}
		} catch (Exception e) {
			try {
				ccxDB.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			rs = 0;
			e.printStackTrace();
		} finally {
			try {
				if (ccxDB != null)
					ccxDB.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return rs;
	}
	
	/**
	 * 查询密码
	 */
	public boolean verifyPassword(String uid,String pwd){
		CcxDB ccxDB = null;
		ResultSet rs = null;
		String userPassword = null;
		try {
			if (uid != null && !uid.equals("")) {
				String sqlKey = "select PASSWORD from C_USER_PASSWORD where USER_ID=?";
				Object[] items = new Object[1];
				items[0] = uid;
				ccxDB = DBFactory.getInstance();
				rs = ccxDB.executeQuery(sqlKey, items);
				if (rs.next()) {
					userPassword = rs.getString("PASSWORD");
				}
				if(userPassword.equals(pwd))
					return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (ccxDB != null)
					ccxDB.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return false;
	}
	
	/**
	 * 修改密码
	 */
    public int updatePassword(String uid,String pwd){
		CcxDB ccxDB = null;
		int rs = 0;
		try{
			if (uid != null && !uid.equals("")) {
				String sqlKey = "update C_USER_PASSWORD set PASSWORD=? where USER_ID=?";
				Object[] items = new Object[2];
				items[0] = pwd;
				items[1] = uid;
				ccxDB = DBFactory.getInstance();
				rs = ccxDB.executeUpdate(sqlKey, items);
			}
		}catch(Exception e){
			e.printStackTrace();
		} finally {
			try {
				if (ccxDB != null)
					ccxDB.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return rs;
	}

	protected void buildSqlParameters(Map<String, String> map) {
		pageSql = SEARCH_ALL_USER;
		System.out.println(SEARCH_ALL_USER);
		//最后登录时间段 精确查询 |根据 公司ID | 用户ID |  ,默认查询全部数据
		String begin = map.get("bDate");
		if (StringValueValid.isNotNullAndEmpty(begin)) {
			pageSql += " and users.LAST_LOGIN_TIME >= to_Date(?,'yyyy-MM-dd') ";
			pageParams.add(begin.trim());
		}
		
		String end = map.get("eDate");
		if (StringValueValid.isNotNullAndEmpty(end)) {
			pageSql += " and users.LAST_LOGIN_TIME <= to_Date(?,'yyyy-MM-dd')  ";
			pageParams.add(end.trim());
		}
		
		String uid = map.get("searchInfo");
		if (StringValueValid.isNotNullAndEmpty(uid)
				&& !"请输入ID".equals(uid)) {
			pageSql += " and users.USER_ID like ? " ;
			pageParams.add("%" + map.get("searchInfo").trim() + "%");
		}

		String cid=map.get("cusID");
		if (StringValueValid.isNotNullAndEmpty(cid)) {
			pageSql +=" and users.customer_id=? " ;
			pageParams.add(cid.trim());
		}
	}

	public static void main(String[] args) {
		UserDaoImpl udi = new UserDaoImpl();
		udi.getReadList("", null);
	}

}
